﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.Security.Cryptography;

namespace InfoLeech
{
    public partial class ctlPMtools : UserControl
    {
        private string connectionStringPM;
        private BindingSource bindingSourcePackage = new BindingSource();
        private BindingSource bindingSourceAMD = new BindingSource();
        private BindingSource bindingSourceDependenciesMaster = new BindingSource();
        private BindingSource bindingSourceDependenciesChild = new BindingSource();
        private BindingSource bindingSourceStatus = new BindingSource();
        private SqlDataAdapter dataAdapterPackage = new SqlDataAdapter();
        private SqlDataAdapter dataAdapterAMD = new SqlDataAdapter();
        private SqlDataAdapter dataAdapterDependenciesMaster = new SqlDataAdapter();
        private SqlDataAdapter dataAdapterDependenciesChild = new SqlDataAdapter();
        private SqlDataAdapter dataAdapterStatus = new SqlDataAdapter();
        private DataTable tablePackage = new DataTable();
        private DataTable tableAMD = new DataTable();
        private DataTable tableDependenciesMaster = new DataTable();
        private DataTable tableDependenciesChild = new DataTable();
        private DataTable tableStatus = new DataTable();
        private int pmIdcWidth = 50;
        private int minSearchStringLength = 3;

        public ctlPMtools()
        {
            InitializeComponent();
            connectionStringPM = frmMain.connectionStringPM;
            // Bind the DataGridView to the BindingSource
            dataGridViewPackage.DataSource = bindingSourcePackage;
            dataGridViewAMD.DataSource = bindingSourceAMD;
            dataGridViewDependenciesMaster.DataSource = bindingSourceDependenciesMaster;
            dataGridViewDependenciesChild.DataSource = bindingSourceDependenciesChild;
            dataGridViewStatus.DataSource = bindingSourceStatus;
            loadSettings();
        }

        public void FillcomboBoxReport()
        {
            SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionStringPM);
            try
            {
                connection.Open();

                DataSet dataSet = new DataSet();
                dataSet.CaseSensitive = true;
                string commandString = "SELECT Name FROM [PM_HAN].[dbo].[Units]" +
                    "order by name";
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = commandString;

                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    // object packageId = dataReader["unitid"];
                    object packageName = dataReader["name"];
                    comboBoxReport.Items.Add(packageName.ToString());
                }
                dataReader.Close();
                comboBoxReport.SelectedIndex = 0;
            }
            catch (SqlException)
            {
                // Process exception and return.
                MessageBox.Show("Error connecting to database.");
            }
        }

        private void GetPackageData()
        {
            if (tbPackage.Text.Length < minSearchStringLength)
            {
                MessageBox.Show("Zoekstring is te kort. Deze moet minimaal " + minSearchStringLength + 
                    " karakters lang zijn.");
            }
            else
            {
                //Clear table
                try
                {
                    tablePackage.Clear();
                }
                catch (DataException e)
                {
                    // Process exception and return.
                    MessageBox.Show(e.ToString());
                }

                try
                {
                    string qryPackage = 
                        "DECLARE @package VARCHAR(100) " +
                        "SET @package = '" + tbPackage.Text + "' " +
                        "DECLARE @temptable TABLE " +
                        "(PM_id INT,Package VARCHAR(100),AMD1 VARCHAR(100),AMD2 VARCHAR(100),Acceptatie VARCHAR(100)," +
	                        "Proces VARCHAR(20),Status VARCHAR(20)) " +
                        "DECLARE @PMid Int " +
                        "DECLARE @tempSearchResult TABLE " +
                        "(PM_id INT) " +
                        "IF ISNUMERIC(@package) = 1 " +
	                    "    insert into @tempSearchResult(PM_id)" +
		                "        select Units.unitid from units where unitid LIKE '%' + @package + '%' " +
                        "ELSE " +
	                    "    insert into @tempSearchResult(PM_id) " +
	                    "    (select Distinct Units.unitid " +
		                "        FROM Units, proces, ProcesData "+
		                "        Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND " +
                        " procesdata.itemid IN (182,335,396) " +
		                "        AND CharIndex(@package, Units.Name) > 0) " +
                        "DECLARE pm_cursor CURSOR FAST_FORWARD FOR " +
                        "    select * from @tempSearchResult " +
                        "OPEN pm_cursor " +
                        "FETCH NEXT FROM pm_cursor INTO @PMid " +
                        "WHILE @@FETCH_STATUS = 0 " +
	                    "    BEGIN " +
		                "        insert into @temptable(	PM_id, Package,	amd1, amd2,	acceptatie,	proces, status) " +
		                "        select " +
			            "            @PMid, Units.Name, " +
			            "            (select procesdata.Itemvalue FROM Units, proces, ProcesData " +
			            "            Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND procesdata.itemid = 182 " +
			            "            AND Units.unitid = @PMid), " +
			            "            (select procesdata.Itemvalue FROM Units, proces, ProcesData " +
			            "            Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND procesdata.itemid = 396 " +
			            "            AND Units.unitid = @PMid), " +
			            "            (select procesdata.Itemvalue FROM Units, proces, ProcesData " +
			            "            Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND procesdata.itemid = 335 " +
			            "            AND Units.unitid = @PMid), " +
			            "            RptHuidigeStatus.[huidig proces], " +
			            "            RptHuidigeStatus.[huidige status] " +
			            "            FROM Units, RptHuidigeStatus " +
			            "            Where Units.unitid = @PMid AND RptHuidigeStatus.UnitId = @PMid " +
		                "        FETCH NEXT FROM pm_cursor INTO @PMid " +
	                    "    END " +
                        "CLOSE pm_cursor " +
                        "DEALLOCATE pm_cursor " +

                        "Select * from @temptable " +
                        "order by package";

                    // Create a new data adapter based on the specified query.
                    dataAdapterPackage = new SqlDataAdapter(qryPackage, connectionStringPM);
                    tablePackage.Locale = System.Globalization.CultureInfo.InvariantCulture;

                    //Fill datatable with query results.
                    dataAdapterPackage.Fill(tablePackage);
                    bindingSourcePackage.DataSource = tablePackage;
                    dataGridViewPackage.Columns[0].Width = pmIdcWidth;
                }
                catch (SqlException)
                {
                    MessageBox.Show("Error connecting to database.");
                }
            }
        }

        private void GetAMDData()
        {
            if (tbAMD.Text.Length < minSearchStringLength)
            {
                MessageBox.Show("Zoekstring is te kort. Deze moet minimaal " + minSearchStringLength + "karakters lang zijn.");
            }
            else
            {
                //Clear table
                try
                {
                    tableAMD.Clear();
                }
                catch (DataException e)
                {
                    // Process exception and return.
                    MessageBox.Show(e.ToString());
                }

                try
                {
                    string qryAMD =
                        "DECLARE @amd VARCHAR(100) " +
                        "SET @amd = '" + tbAMD.Text + "' " +
                        "DECLARE @temptable TABLE " +
                        "( " +
	                    "    PM_id INT, " +
	                    "    Package VARCHAR(100)," +
	                    "    AMD1 VARCHAR(100)," +
	                    "    AMD2 VARCHAR(100)," +
	                    "    Acceptatie VARCHAR(100)," +
	                    "    Proces VARCHAR(20)," +
	                    "    Status VARCHAR(20)" +
                        ") " +
                        "DECLARE @PMid Int " +
                        "DECLARE pm_cursor CURSOR FAST_FORWARD FOR " +
	                    "    SELECT Distinct Units.unitid FROM Units, proces, ProcesData " +
	                    "    Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND " +
	                    "    procesdata.itemid IN (182,335,396) AND Units.unitid IN " +
	                    "    (SELECT Distinct(Units.unitid) FROM Units, proces, ProcesData " +
	                    "    Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND " +
	                    "    procesdata.itemid IN (182,335,396) AND CharIndex(@amd, procesdata.Itemvalue) > 0) " +
                        "OPEN pm_cursor " +
                        "FETCH NEXT FROM pm_cursor INTO @PMid " +
                        "WHILE @@FETCH_STATUS = 0 " +
	                    "    BEGIN " +
		                "        insert into @temptable(	PM_id, Package,	amd1, amd2,	acceptatie,	proces, status) " +
		                "        select " +
			            "            @PMid, Units.Name," +
			            "            (select procesdata.Itemvalue FROM Units, proces, ProcesData " +
			            "            Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND procesdata.itemid = 182 " +
			            "            AND Units.unitid = @PMid), " +
			            "            (select procesdata.Itemvalue FROM Units, proces, ProcesData " +
			            "            Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND procesdata.itemid = 396 " +
			            "            AND Units.unitid = @PMid), " +
			            "            (select procesdata.Itemvalue FROM Units, proces, ProcesData " +
			            "            Where Units.unitid = proces.unitid AND proces.procesid = procesdata.procesid AND procesdata.itemid = 335 " +
			            "            AND Units.unitid = @PMid), " +
			            "            RptHuidigeStatus.[huidig proces], " +
			            "            RptHuidigeStatus.[huidige status] " +
			            "            FROM Units, RptHuidigeStatus " +
			            "            Where Units.unitid = @PMid AND RptHuidigeStatus.UnitId = @PMid " +
		                "        FETCH NEXT FROM pm_cursor INTO @PMid " +
	                    "    END " +
                        "CLOSE pm_cursor " +
                        "DEALLOCATE pm_cursor " +
                        "Select * from @temptable " +
                        "order by package";

                    // Create a new data adapter based on the specified query.
                    dataAdapterAMD = new SqlDataAdapter(qryAMD, connectionStringPM);
                    tableAMD.Locale = System.Globalization.CultureInfo.InvariantCulture;

                    //Fill datatable with query results.
                    dataAdapterAMD.Fill(tableAMD);
                    bindingSourceAMD.DataSource = tableAMD;
                    dataGridViewAMD.Columns[0].Width = pmIdcWidth;
                }
                catch (SqlException)
                {
                    MessageBox.Show("Error connecting to database\n" +
                        "Check the connectionstring.");
                }
            }
        }

        private void GetDependenciesData()
        {
            if (tbDependencies.Text.Length < minSearchStringLength)
            {
                MessageBox.Show("Zoekstring is te kort. Deze moet minimaal " + minSearchStringLength + "karakters lang zijn.");
            }
            else
            {
                //Clear table
                try
                {
                    tableDependenciesMaster.Clear();
                }
                catch (DataException e)
                {
                    // Process exception and return.
                    MessageBox.Show(e.ToString());
                }

                try
                {
                    string qryDependenciesMaster =
                        "SELECT unitid PM_Id, name FROM [PM_HAN].[dbo].[Units] where CharIndex('" + 
                        tbDependencies.Text + "', Name) > 0";
                    // Create a new data adapter based on the specified query.
                    dataAdapterDependenciesMaster = new SqlDataAdapter(qryDependenciesMaster, connectionStringPM);
                    tableDependenciesMaster.Locale = System.Globalization.CultureInfo.InvariantCulture;

                    //Fill datatable with query results.
                    dataAdapterDependenciesMaster.Fill(tableDependenciesMaster);
                    bindingSourceDependenciesMaster.DataSource = tableDependenciesMaster;
                    dataGridViewDependenciesMaster.Columns[0].Width = pmIdcWidth;
                }
                catch (SqlException)
                {
                    MessageBox.Show("Error connecting to database\n" +
                        "Check the connectionstring.");
                }
                GetDependenciesChildData(tbDependencies.Text);
            }
        }

        private void GetDependenciesChildData(string strDepenencies)
        {
                //Clear table
                try
                {
                    tableDependenciesChild.Clear();
                }
                catch (DataException e)
                {
                    // Process exception and return.
                    MessageBox.Show(e.ToString());
                }

                try
                {
                    string qryDependenciesChild =
                        "SELECT unitid AS PM_Id, name AS Package, 'Selected items' AS Invoer " +
	                    "FROM [PM_HAN].[dbo].[Units] where unitid IN " +
	                    "(SELECT unitid FROM [PM_HAN].[dbo].[Proces] where procesid IN " +
		                "(SELECT ProcesId FROM [PM_HAN].[dbo].[SelectedItem] where itemindex IN " +
			            "(SELECT unitid FROM [PM_HAN].[dbo].[Units] where CharIndex('" +
                        strDepenencies + "', Name) > 0))) " +
                        "UNION " +
                        "SELECT unitid AS PM_Id, name AS Package, 'Getypte items' AS Invoer " +
	                    "FROM [PM_HAN].[dbo].[Units] where unitid IN " +
	                    "(SELECT unitid FROM [PM_HAN].[dbo].[Proces] where procesid IN " +
		                "(SELECT ProcesId FROM procesdata WHERE ItemId IN (497,499,519,193,194,517) AND " +
                        "(CharIndex('" + strDepenencies + "', itemvalue) > 0 OR CharIndex('" + 
                        strDepenencies + "', itemvaluem) > 0))) " +
                        "ORDER BY name";
                    // Create a new data adapter based on the specified query.
                    dataAdapterDependenciesChild = new SqlDataAdapter(qryDependenciesChild, connectionStringPM);
                    tableDependenciesChild.Locale = System.Globalization.CultureInfo.InvariantCulture;

                    //Fill datatable with query results.
                    dataAdapterDependenciesChild.Fill(tableDependenciesChild);
                    bindingSourceDependenciesChild.DataSource = tableDependenciesChild;
                    dataGridViewDependenciesChild.Columns[0].Width = pmIdcWidth;
                }
                catch (SqlException)
                {
                    MessageBox.Show("Error connecting to database\n" +
                        "Check the connectionstring.");
                }
            }

        private void GetStatusData()
        {
            if (selectedStatus().Length == 0)
            {
                MessageBox.Show("Selecteer minimaal één Status!");
            }
            else
            {
                //Clear table
                try
                {
                    tableStatus.Clear();
                }
                catch (DataException e)
                {
                    // Process exception and return.
                    MessageBox.Show(e.ToString());
                }

                try
                {
                    string qryStatus =
                        "select UnitId PM_id,Name, [Huidig Proces], [Huidige Status] from rpthuidigestatus " +
                        "where [Huidig Proces] = '" + comboBoxProces.SelectedItem +
                        "' AND [huidige status] IN (" + selectedStatus() + ") ORDER BY [huidige status]";
                    //MessageBox.Show(selectedStatus());
                    // Create a new data adapter based on the specified query.
                    dataAdapterStatus = new SqlDataAdapter(qryStatus, connectionStringPM);
                    tableStatus.Locale = System.Globalization.CultureInfo.InvariantCulture;

                    //Fill datatable with query results.
                    dataAdapterStatus.Fill(tableStatus);
                    bindingSourceStatus.DataSource = tableStatus;
                    dataGridViewStatus.Columns[0].Width = pmIdcWidth;
                }
                catch (SqlException)
                {
                    MessageBox.Show("Error connecting to database\n" +
                        "Check the connectionstring.");
                }
            }
        }

        private void GetReportData(string pkgName)
        {
            // Clear all textboxes
            tbDataPath.Text = "";
            tbUserDataPath.Text = "";
            tbProgramDataPath.Text = "";
            tbPackageName.Text = "";
            tbTechnician.Text = "";
            tbRemarks.Text = "";
            tbStatus.Text = "";
            tbInstallSoftware1.Text = "";
            tbInstallSoftware2.Text = "";
            tbInstallSoftware3.Text = "";
            tbInstallOther.Text = "";
            tbInstallHardware.Text = "";
            tbUsageSoftware1.Text = "";
            tbUsageSoftware2.Text = "";
            tbUsageSoftware3.Text = "";
            tbUsageOther.Text = "";
            tbUsageHardware.Text = "";
            tbPreInstall.Text = "";
            tbInstall.Text = "";
            tbPostInstall.Text = "";
            dataGridViewNTFS.Rows.Clear();
            dataGridViewRegistry.Rows.Clear();
            tbDesignImplementation.Text = "";
            tbVerifyInstall.Text = "";
            tbKnownErrors.Text = "";

            // Test
            // string selectedPackage = comboBoxReport.SelectedItem.ToString();
            if (pkgName == null || pkgName =="")
                MessageBox.Show("Verkeerde selectie!");
            else
            {
                Int16 PM_id = 0;
                SqlConnection myConnection = new SqlConnection(connectionStringPM);

                // Get the data
                try
                {
                    // First get the PackageId
                    string qPackageId = "SELECT UnitId AS PM_id FROM [PM_HAN].[dbo].[Units] where name = '" +
                        pkgName + "'";
                    SqlCommand myCommand = new SqlCommand(qPackageId, myConnection);
                    myConnection.Open();
                    SqlDataReader myReader = myCommand.ExecuteReader();
                    while (myReader.Read())
                    {
                        PM_id = Convert.ToInt16(myReader["PM_id"]);
                    }
                    myReader.Close();

                    // Package name + version
                    string qPackageNameVersion = "SELECT Name AS PackageName FROM [PM_HAN].[dbo].[Units] where unitid = " + PM_id;

                    // Package gemaakt door
                    string qTechnician = "SELECT " + 
                        "(SELECT REPLACE(dbo.Users.Firstname + ' ' + dbo.Users.MiddleName + ' ' + dbo.Users.LastName, '  ', ' ') " +
                        "FROM dbo.Users INNER JOIN dbo.SelectedItem ON dbo.Users.UserID = dbo.SelectedItem.ItemIndex INNER JOIN " +
                        "dbo.Proces ON dbo.SelectedItem.ProcesId = dbo.Proces.ProcesId " +
                        "WHERE (dbo.Proces.UnitId = u.UnitId) AND (dbo.Proces.ProcesTypeId = 18)) AS [Technician] " +
                        "FROM dbo.Units AS u where u.UnitId = " + @PM_id;

                    // Locatie van Bestanden
                    string qDataPath = "select ItemValue AS DataPath from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 192 AND itemindex = 1)";

                    // Locatie van Gebruikers bestanden
                    string qUserDataPath = "select ItemValue AS UserDataPath from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 192 AND itemindex = 2)";

                    //Locatie van Programma bestanden
                    string qProgramDataPath = "select ItemValue AS ProgramDataPath from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 192 AND itemindex = 0)";

                    // Status & Remarks
                    string qRemarks = "Select statusremarks from Proces where unitid = " + PM_id + " AND procestypeid = 18";

                    // Huidige status
                    string qStatus = "Select [Huidig Proces] + ' - ' + [Huidige Status] AS CurrentStatus " +
                        "from [PM_HAN].[dbo].[RptHuidigeStatus] where unitid = " + PM_id;

                    // Voorwaarden installatie software 1 
                    string qPreReqInstallSoftware1 = "SELECT name AS PreReqInstallSoftware1 FROM Units where unitid IN " +
                        "(SELECT itemindex FROM SelectedItem where procesid IN " +
                        "(Select procesid from Proces where unitid = " + PM_id + ") AND itemid = 515)";

                    // Voorwaarden installatie software 2
                    string qPreReqInstallSoftware2 = "SELECT name AS PreReqInstallSoftware2 FROM Units where unitid IN " +
                        "(SELECT itemindex FROM SelectedItem where procesid IN " +
                        "(Select procesid from Proces where unitid = " + PM_id + ") AND itemid = 516)";

                    // Voorwaarden installatie software 3
                    string qPreReqInstallSoftware3 = "select itemvalue AS PreReqInstallSoftware3 from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 517)";

                    // Voorwaarden installatie overig
                    string qPreReqInstallOther = "select itemvaluem AS PreReqInstallOther from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 194)";

                    // Voorwaarden installatie hardware
                    string qPreReqInstallHardware = "select itemvaluem AS PreReqInstallHardware from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid =193)";

                    // Voorwaarden gebruik software 1
                    string qPreReqUsageSoftware1 = "SELECT name AS PreReqUsageSoftware1 FROM Units where unitid IN " +
                        "(SELECT itemindex FROM SelectedItem where procesid IN " +
                        "(Select procesid from Proces where unitid = " + PM_id + ") AND itemid = 498)";

                    // Voorwaarden gebruik software 2
                    string qPreReqUsageSoftware2 = "SELECT name AS PreReqUsageSoftware2 FROM Units where unitid IN " +
                        "(SELECT itemindex FROM SelectedItem where procesid IN " +
                        "(Select procesid from Proces where unitid = " + PM_id + ") AND itemid = 518)";

                    // Voorwaarden gebruik software 3
                    string qPreReqUsageSoftware3 = "select itemvalue AS PreReqUsageSoftware3 from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 519)";

                    // Voorwaarden Gebruik overig
                    string qPreReqUsageOther = "select itemvaluem AS PreReqUsageOther from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 499)";

                    // Voorwaarden gebruik hardware
                    string qPreReqUsageHardware = "select itemvaluem AS PreReqUsageHardware from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 497)";

                    // Pre-installatie voorwaarden
                    string qPreInstall = "select itemvaluem AS PreInstall from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 200)";

                    // Installatie voorwaarden
                    string qInstall = "select itemvaluem AS Install from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 201)";

                    // Post-installatie voorwaarden
                    string qPostInstall = "select itemvaluem AS PostInstall from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 202)";

                    // NTFS rechten
                    string qNTFS = "select itemvalue, itemindex INTO #t1 from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid = 204 AND itemcol = 0) " +
                        "select itemvalue, itemindex INTO #t2 from procesdata where procesid IN " +
                            "(select procesid from proces where unitid = " + PM_id + " AND itemid = 204 AND itemcol = 2) " +
                        "select #t1.itemvalue AS #, #t2.itemvalue AS Path from #t1 " +
                        "JOIN #t2 ON #t1.itemindex = #t2.itemindex ORDER BY # " +
                        "drop table #t1, #t2";

                    // Registry rechten
                    string qRegistry = "select itemvalue AS RegistryKey from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid =206 AND itemcol = 1)";

                    // Ontwerp & Implementatie beslissingen
                    string qDesignImplementation = "select itemvaluem AS DesignImplementation from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid =208)";

                    // Controle Installatie
                    string qVerifyInstall = "select itemvaluem AS VerifyInstall from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid =207)";

                    // Known Errors
                    string qKnownErrors = "select itemvaluem AS KnownErrors from procesdata where procesid IN " +
                        "(select procesid from proces where unitid = " + PM_id + " AND itemid =209)";

                    // Fill in the report
                    // Packagename
                    tbPackageName.Text = PM_id + "  " + pkgName;

                    // Statusremarks
                    tbRemarks.Text = getFieldData(qRemarks, "statusremarks");

                    // Technician
                    tbTechnician.Text = getFieldData(qTechnician, "Technician");

                    // Huidige status
                    tbStatus.Text = getFieldData(qStatus, "CurrentStatus");

                    // Locatie Bestanden
                    tbDataPath.Text = getFieldData(qDataPath, "DataPath");

                    // Locatie Gebruikers Bestanden
                    tbUserDataPath.Text = getFieldData(qUserDataPath, "UserDataPath");

                    // Locatie Programma Bestanden
                    tbProgramDataPath.Text = getFieldData(qProgramDataPath, "ProgramDataPath");

                    // Voorwaarde installatie software 1
                    tbInstallSoftware1.Text = getFieldData(qPreReqInstallSoftware1, "PreReqInstallSoftware1");

                    // Voorwaarde installatie software 2
                    tbInstallSoftware2.Text = getFieldData(qPreReqInstallSoftware2, "PreReqInstallSoftware2");

                    // Voorwaarde installatie software 3
                    tbInstallSoftware3.Text = getFieldData(qPreReqInstallSoftware3, "PreReqInstallSoftware3");

                    // Voorwaarde installatie hardware
                    tbInstallHardware.Text = getFieldData(qPreReqInstallHardware, "PreReqInstallHardware");

                    // Voorwaarde installatie overig
                    tbInstallOther.Text = getFieldData(qPreReqInstallOther, "PreReqInstallOther");

                    // Voorwaarde gebruik software 1
                    tbUsageSoftware1.Text = getFieldData(qPreReqUsageSoftware1, "PreReqUsageSoftware1");

                    // Voorwaarde gebruik software 2
                    tbUsageSoftware2.Text = getFieldData(qPreReqUsageSoftware2, "PreReqUsageSoftware2");

                    // Voorwaarde gebruik software 3
                    tbUsageSoftware3.Text = getFieldData(qPreReqUsageSoftware3, "PreReqUsageSoftware3");

                    // Voorwaarde gebruik Hardware
                    tbUsageHardware.Text = getFieldData(qPreReqUsageHardware, "PreReqUsageHardware");

                    // Voorwaarde gebruik overig
                    tbUsageOther.Text = getFieldData(qPreReqUsageOther, "PreReqUsageOther");

                    // Pre Install
                    tbPreInstall.Text = getFieldData(qPreInstall, "PreInstall");

                    // Install
                    tbInstall.Text = getFieldData(qInstall, "Install");

                    // Post Install
                    tbPostInstall.Text = getFieldData(qPostInstall, "PostInstall");

                    // NTFS
                    myCommand.CommandText = qNTFS;
                    SqlDataReader myReader1 = myCommand.ExecuteReader();
                    while (myReader1.Read())
                    {
                        this.dataGridViewNTFS.Rows.Add(myReader1["#"].ToString(), myReader1["Path"].ToString());
                    }
                    dataGridViewNTFS.ClearSelection();
                    myReader1.Close();

                    // Registry
                    myCommand.CommandText = qRegistry;
                    SqlDataReader myReader2 = myCommand.ExecuteReader();
                    while (myReader2.Read())
                    {
                        this.dataGridViewRegistry.Rows.Add(myReader2["RegistryKey"].ToString());
                    }
                    dataGridViewRegistry.ClearSelection();
                    myReader2.Close();

                    // Design and Implementation
                    tbDesignImplementation.Text = getFieldData(qDesignImplementation, "DesignImplementation");

                    // Verify Install
                    tbVerifyInstall.Text = getFieldData(qVerifyInstall, "VerifyInstall");

                    // Known Errors
                    tbKnownErrors.Text = getFieldData(qKnownErrors, "KnownErrors");

                    // Update statuslabel
                    ((frmMain)Application.OpenForms["frmMain"]).updateStatusLabel(pkgName);
                }
                catch (SqlException)
                {
                    // Process exception and return.
                    MessageBox.Show("Error generating report.");
                }
            }
        }

        private string getFieldData(string query, string field)
        {
            string result = "";
            SqlConnection myConnection = new SqlConnection(connectionStringPM);
            SqlCommand myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            myCommand.CommandText = query;
            SqlDataReader myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                result = myReader[field].ToString();
            }
            myReader.Close();
            myConnection.Close();
            return result;
        }

        private void btnSearchPackage_Click(object sender, EventArgs e)
        {
            {
                GetPackageData();
            }
        }

        private void tbPackage_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Return)
            {
                GetPackageData();
            }
        }

        private void btnSearchAMD_Click(object sender, EventArgs e)
        {
            GetAMDData();
        }

        private void tbAMD_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Return)
            {
                GetAMDData();
            }

        }

        private void btnSearchDependencies_Click(object sender, EventArgs e)
        {
            GetDependenciesData();
        }

        private void tbDependencies_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Return)
            {
                GetDependenciesData();
            }
        }

        private void btnSearchStatus_Click(object sender, EventArgs e)
        {
            GetStatusData();
        }

        private string selectedStatus()
        {
            string statusList = "";
            foreach (Control c in this.tableLayoutPanelStatus.Controls)
            {
                CheckBox chkBox = c as CheckBox;
                if (chkBox.Checked)
                {
                    statusList += "'" + c.AccessibleName + "',";
                }
            }
            if (statusList.Length > 0)
            {
                statusList = statusList.Substring(0, statusList.Length - 1);
            }
            return statusList;
        }

        private void selectAllToolStripMenuItem_Click(object sender, EventArgs e)
        {
            // The context menu strip called this method, so we need to determine which DGV it was clicked on    
            ToolStripDropDownItem item = sender as ToolStripDropDownItem;
            if (item == null) // Error            
                return;
            ContextMenuStrip strip = item.Owner as ContextMenuStrip;
            DataGridView grid = strip.SourceControl as DataGridView;
            if (grid == null) // Control wasn't a DGV
                return;
            grid.SelectAll();
        }

        private void copyToolStripMenuItem_Click(object sender, EventArgs e)
        {
            // The context menu strip called this method, so we need to determine which DGV it was clicked on    
            ToolStripDropDownItem item = sender as ToolStripDropDownItem;
            if (item == null) // Error            
                return;
            ContextMenuStrip strip = item.Owner as ContextMenuStrip;
            DataGridView grid = strip.SourceControl as DataGridView;
            if (grid == null) // Control wasn't a DGV
                return;
            DataObject data = grid.GetClipboardContent();
            if (data != null)
                Clipboard.SetDataObject(data);
        }

        private void tbPMid_Report_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Return)
            {
                // GetReportData();
            }
        }

        private void buttonReport_Click(object sender, EventArgs e)
        {
            string selectedPackage = comboBoxReport.SelectedItem.ToString();
            GetReportData(selectedPackage);
        }

        private void dataGridViewStatus_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Int32 selectedCellCount =
                dataGridViewStatus.GetCellCount(DataGridViewElementStates.Selected);
            if (selectedCellCount > 0)
            {
                if (e.RowIndex >= 0)
                {
                    if (dataGridViewStatus.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                    {
                        string pkgName = dataGridViewStatus.Rows[e.RowIndex].Cells[1].Value.ToString();
                        GetReportData(pkgName);
                        this.tabControlPM.SelectedTab = tabPageReport;
                        comboBoxReport.SelectedItem = pkgName;
                    }
                }
            }
        }

        private void dataGridViewPackage_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Int32 selectedCellCount =
                dataGridViewPackage.GetCellCount(DataGridViewElementStates.Selected);
            if (selectedCellCount > 0)
            {
                if (e.RowIndex >= 0)
                {
                    if (dataGridViewPackage.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                    {
                        string pkgName = dataGridViewPackage.Rows[e.RowIndex].Cells[1].Value.ToString();
                        GetReportData(pkgName);
                        this.tabControlPM.SelectedTab = tabPageReport;
                        comboBoxReport.SelectedItem = pkgName;
                    }
                }
            }
        }

        private void dataGridViewDependenciesChild_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Int32 selectedCellCount =
                dataGridViewDependenciesChild.GetCellCount(DataGridViewElementStates.Selected);
            if (selectedCellCount > 0)
            {
                if (e.RowIndex >= 0)
                {
                    if (dataGridViewDependenciesChild.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                    {
                        string pkgName = dataGridViewDependenciesChild.Rows[e.RowIndex].Cells[1].Value.ToString();
                        GetReportData(pkgName);
                        this.tabControlPM.SelectedTab = tabPageReport;
                        comboBoxReport.SelectedItem = pkgName;
                    }
                }
            }
        }

        private void dataGridViewDependenciesMaster_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Int32 selectedCellCount =
                dataGridViewDependenciesMaster.GetCellCount(DataGridViewElementStates.Selected);
            if (selectedCellCount > 0)
            {
                if (e.RowIndex >= 0)
                {
                    if (dataGridViewDependenciesMaster.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                    {
                        string pkgName = dataGridViewDependenciesMaster.Rows[e.RowIndex].Cells[1].Value.ToString();
                        tbDependencies.Text = pkgName;
                        GetDependenciesData();
                    }
                }
            }
        }

        private void dataGridViewAMD_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Int32 selectedCellCount =
                dataGridViewAMD.GetCellCount(DataGridViewElementStates.Selected);
            if (selectedCellCount > 0)
            {
                if (e.RowIndex >= 0)
                {
                    if (dataGridViewAMD.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                    {
                        string pkgName = dataGridViewAMD.Rows[e.RowIndex].Cells[1].Value.ToString();
                        GetReportData(pkgName);
                        this.tabControlPM.SelectedTab = tabPageReport;
                        comboBoxReport.SelectedItem = pkgName;
                    }
                }
            }
        }

        private void loadSettings()
        {
            comboBoxProces.SelectedIndex = Properties.Settings.Default.comboProces;
            checkBoxStatusWaiting.Checked = Properties.Settings.Default.cbWaiting;
            checkBoxStatusInProgres.Checked = Properties.Settings.Default.cbInProgress;
            checkBoxStatusRejected.Checked = Properties.Settings.Default.cbRejected;
            checkBoxStatusExpired.Checked = Properties.Settings.Default.cbExpired;
            checkBoxStatusCancelled.Checked = Properties.Settings.Default.cbCancelled;
            checkStatusOnHold.Checked = Properties.Settings.Default.cbOnHold;
        }

        private void comboBoxProces_SelectedIndexChanged(object sender, EventArgs e)
        {
            Properties.Settings.Default.comboProces = comboBoxProces.SelectedIndex;
            Properties.Settings.Default.Save();
        }

        private void checkBoxStatusWaiting_CheckedChanged(object sender, EventArgs e)
        {
            Properties.Settings.Default.cbWaiting = checkBoxStatusWaiting.Checked;
            Properties.Settings.Default.Save();
        }

        private void checkBoxStatusInProgres_CheckedChanged(object sender, EventArgs e)
        {
            Properties.Settings.Default.cbInProgress = checkBoxStatusInProgres.Checked;
            Properties.Settings.Default.Save();
        }

        private void checkStatusOnHold_CheckedChanged(object sender, EventArgs e)
        {
            Properties.Settings.Default.cbOnHold = checkStatusOnHold.Checked;
            Properties.Settings.Default.Save();
        }

        private void checkBoxStatusRejected_CheckedChanged(object sender, EventArgs e)
        {
            Properties.Settings.Default.cbRejected = checkBoxStatusRejected.Checked;
            Properties.Settings.Default.Save();
        }

        private void checkBoxStatusCancelled_CheckedChanged(object sender, EventArgs e)
        {
            Properties.Settings.Default.cbCancelled = checkBoxStatusCancelled.Checked;
            Properties.Settings.Default.Save();
        }

        private void checkBoxStatusExpired_CheckedChanged(object sender, EventArgs e)
        {
            Properties.Settings.Default.cbExpired = checkBoxStatusExpired.Checked;
            Properties.Settings.Default.Save();
        }
    }
}
